<?php

import ( 'classes.acct.CTKTTH' );

class CTKTTHDAO extends DAO {
	function &getCTKTTH_view($query, $paramArray, $rangeInfo) {
		$result =& $this->retrieveRange($query, $paramArray, $rangeInfo);

		//$result = & $this->retrieve ( $query, $paramArray );
		//$returner = new DAOResultFactory($result, $this, '_returnChuyengiaFromRow');
		//return $returner;

		$returner = new DAOResultFactory ( $result, $this, '_returnCTKTTH_view_FromRow' );
		return $returner;

	}



	function &_returnCTKTTH_view_FromRow(&$row) {
		$CTKTTH = new CTKTTH();
		$CTKTTH ->setidphieu($row['idphieu']);
		$CTKTTH->setsoct($row['soct']);
		$CTKTTH->setngct($row['ngct']);
		$CTKTTH->setdiengiai($row['diengiai']);
		$CTKTTH->settkno($row['tkno']);
		$CTKTTH->settkco($row['tkco']);
		$CTKTTH->setmacttkno($row['macttkno']);
		$CTKTTH->setmacttkco($row['macttkco']);
		$CTKTTH->setsotienvn($row['sotienvn']);
		return $CTKTTH;
	}


	function &getCTKTTH_viewNT($query, $paramArray) {
		//echo $query;
		$result = & $this->retrieve ( $query, $paramArray );

		$sectionsIterator = new DAOResultFactory ( $result, $this, '_returnCTKTTH_viewNT_FromRow' );
		
		$sections = $returner;
		$i = 0;
		while ( ($section = & $sectionsIterator->next ()) ) {
			$sections [$i] ['idphieu'] = $section->getidphieu ();
			$sections [$i] ['ngct'] = $section->getngct ();
			$sections [$i] ['soct'] = $section->getsoct ();
			$sections [$i] ['diengiai'] = $section->getdiengiai ();
			$sections [$i] ['tkdu'] = $section->gettkdu ();
			$sections [$i] ['macttkdu'] = $section->getmacttkdu ();
			$sections [$i] ['tk'] = $section->gettk();
			$sections [$i] ['sotienvn'] = $section->getsotienvn ();
			$sections [$i] ['sotiennt'] = $section->getsotiennt ();
			$i += 1;
			unset ( $section );
		}
		return $sections;
	}

	function &_returnCTKTTH_viewNT_FromRow(&$row) {
		$CTKTTH = new CTKTTH();
		$CTKTTH ->setidphieu($row['idphieu']);
		$CTKTTH->setsoct($row['soct']);
		$CTKTTH->setngct($row['ngct']);
		$CTKTTH->setdiengiai($row['diengiai']);
		$CTKTTH->settk($row['tk']);
		$CTKTTH->settkdu($row['tkdu']);
		$CTKTTH->setmacttkdu($row['macttkdu']);
		$CTKTTH->setsotienvn($row['sotienvn']);
		$CTKTTH->setsotiennt($row['sotiennt']);
		return $CTKTTH;
	}


	function &getCTKTTH_edit($query) {
		$result = & $this->retrieve( $query );

		
		$sectionsIterator = new DAOResultFactory ( $result, $this, '_returnCTKTTH_edit_FromRow' );
		
		$sections = $returner;
		$i = 0;
		while ( ($section = & $sectionsIterator->next ()) ) {
			$sections [$i] ['ngct'] = $section->getngct ();
			$sections [$i] ['soct'] = $section->getsoct ();
			$sections [$i] ['diengiai'] = $section->getdiengiai ();
			$sections [$i] ['tkno'] = $section->gettkno ();
			$sections [$i] ['macttkno'] = $section->getmacttkno ();
			$sections [$i] ['tkco'] = $section->gettkco ();
			$sections [$i] ['macttkco'] = $section->getmacttkco ();
			$sections [$i] ['sotienvn'] = $section->getsotienvn ();
			$i += 1;
			unset ( $section );
		}
		return $sections;
	}

	function &_returnCTKTTH_edit_FromRow(&$row) {
		$CTKTTH = new CTKTTH();
		$CTKTTH ->setidphieu($row['idphieu']);
		$CTKTTH->setsoct($row['soct']);
		$CTKTTH->setngct($row['ngct']);
		$CTKTTH->setlydo($row['lydo']);
		$CTKTTH->settkno($row['tkno']);
		$CTKTTH->settkco($row['tkco']);
		$CTKTTH->setmacttkno($row['macttkno']);
		$CTKTTH->setmacttkco($row['macttkco']);
		$CTKTTH->setsotienvn($row['sotienvn']);
		$CTKTTH->setdiengiai($row['diengiai']);
		return $CTKTTH;

	}
function &getBCTC_SDDK($journalid, $nam) {
		$sections = $returner;
		$query = "SELECT matk, dknovn, dkcovn  FROM SDTK" .$journalid. $nam ;
		$result = & $this->retrieve ( $query );
		$returner = array();
		while (!$result->EOF) {
				$row = $result->GetRowAssoc(false);
				$returner[$row['matk']]['dknovn'] = $row['dknovn'];
				$returner[$row['matk']]['dkcovn'] = $row['dkcovn'];

				$result->moveNext();
		}
		$result->Close();
		unset($result);
		return $returner;

}

function &getBCTC_PS($journalid, $nam, $ngay1, $ngay2, $ectkt, $ectktth) {
	if ($ectkt && $ectktth)
	{
	$query = 	
	   "SELECT @s:=@s+1 stt, a.idphieu,  a.soct, a.ngct, b.diengiai,  b.macttkdu as macttk, 
		CASE a.noco
		   WHEN 'N' THEN a.tk
		   WHEN 'C' THEN b.tkdu
		   END AS tkno,
	  CASE a.noco
		   WHEN 'N' THEN b.tkdu
		   WHEN 'C' THEN a.tk
		   END AS tkco,
		   b.sotienvn, b.id
	   FROM (SELECT @s:= 0) AS s , CTKT".$journalid. $nam . " a LEFT JOIN CTKTDT".$journalid. $nam . " b ON (a.idphieu = b.idphieu) WHERE (a.ngct BETWEEN '".$ngay1."' AND '".$ngay2."')
	union
	SELECT @s:=@s+1 stt, a.idphieu, a.soct, a.ngct, b.diengiai, 'ktth' as macttkdu,  b.tkno, b.tkco,  b.sotienvn, b.idphieu as id
	   FROM (SELECT @s:= 0) AS s , ctktth".$journalid. $nam . "  a LEFT JOIN CTKTthdt".$journalid. $nam . " b ON (a.idphieu = b.idphieu) WHERE (a.ngct BETWEEN '".$ngay1."' AND '".$ngay2."') 
	   Order by ngct, soct, id";
	}
	elseif ($ectkt && !$ectktth)
		$query = 	
	   "SELECT @s:=@s+1 stt, '".$tk."' as tk,  a.soct, a.ngct, b.diengiai,  b.macttkdu as macttk, 
		CASE a.noco
		   WHEN 'N' THEN a.tk
		   WHEN 'C' THEN b.tkdu
		   END AS tkno,
	  CASE a.noco
		   WHEN 'N' THEN b.tkdu
		   WHEN 'C' THEN a.tk
		   END AS tkco,
		   b.sotienvn, b.id
	   FROM (SELECT @s:= 0) AS s , CTKT".$journalid. $nam . " a LEFT JOIN CTKTDT".$journalid. $nam . " b ON (a.idphieu = b.idphieu) WHERE (a.ngct BETWEEN '".$ngay1."' AND '".$ngay2."')   Order by ngct, soct, id";
	  else if (!$ectkt && $ectktth)
	$query = 	
	   " SELECT @s:=@s+1 stt, '".$tk."' as tk, a.soct, a.ngct, b.diengiai, 'ktth' as macttkdu,  b.tkno, b.tkco,  b.sotienvn, b.idphieu as id
	   FROM (SELECT @s:= 0) AS s , ctktth".$journalid. $nam . "  a LEFT JOIN CTKTthdt".$journalid. $nam . " b ON (a.idphieu = b.idphieu) WHERE (a.ngct BETWEEN '".$ngay1."' AND '".$ngay2."') Order by ngct, soct, id";
	////
		$result = & $this->retrieve ( $query );

		$sectionsIterator = new DAOResultFactory ( $result, $this, '_returnCTKTTH_socai_FromRow' );

		$sections = $returner;
		$k = 0;
		while ( ($section = & $sectionsIterator->next ()) ) {
				$tkno = $section->gettkno ();
				$tkco = $section->gettkco ();
				$sotien = $section->getsotienvn ();
				
				$sections [$tkno] ['psnovn'] = $sections [$tkno] ['psnovn']+ $sotien;
				$sections [$tkco] ['pscovn'] = $sections [$tkco] ['pscovn']+ $sotien;
				unset ( $section );
		}

		return $sections;
		
	}

function &getCTKTTH_STHTCG($noco, $journalid, $nam, $ngay1, $ngay2, $tk, $ectkt, $ectktth) {
	if ($ectkt && $ectktth)
	{
	$query = 	
	   "SELECT @s:=@s+1 stt, a.idphieu,  a.soct, a.ngct, b.diengiai,  b.macttkdu as macttk, 
		CASE a.noco
		   WHEN 'N' THEN a.tk
		   WHEN 'C' THEN b.tkdu
		   END AS tkno,
	  CASE a.noco
		   WHEN 'N' THEN b.tkdu
		   WHEN 'C' THEN a.tk
		   END AS tkco,
		   b.sotienvn, b.id
	   FROM (SELECT @s:= 0) AS s , CTKT".$journalid. $nam . " a LEFT JOIN CTKTDT".$journalid. $nam . " b ON (a.idphieu = b.idphieu) WHERE (a.ngct BETWEEN '".$ngay1."' AND '".$ngay2."') and (a.tk like '".$tk."%'  or b.tkdu like '".$tk."%' )
	union
	SELECT @s:=@s+1 stt, a.idphieu, a.soct, a.ngct, b.diengiai, 'ktth' as macttkdu,  b.tkno, b.tkco,  b.sotienvn, b.idphieu as id
	   FROM (SELECT @s:= 0) AS s , ctktth".$journalid. $nam . "  a LEFT JOIN CTKTthdt".$journalid. $nam . " b ON (a.idphieu = b.idphieu) WHERE (a.ngct BETWEEN '".$ngay1."' AND '".$ngay2."') and (b.tkno like '".$tk."%'  or b.tkco like '".$tk."%' )
	   Order by ngct, soct, id";
	}
	elseif ($ectkt && !$ectktth)
		$query = 	
	   "SELECT @s:=@s+1 stt, '".$tk."' as tk,  a.soct, a.ngct, b.diengiai,  b.macttkdu as macttk, 
		CASE a.noco
		   WHEN 'N' THEN a.tk
		   WHEN 'C' THEN b.tkdu
		   END AS tkno,
	  CASE a.noco
		   WHEN 'N' THEN b.tkdu
		   WHEN 'C' THEN a.tk
		   END AS tkco,
		   b.sotienvn, b.id
	   FROM (SELECT @s:= 0) AS s , CTKT".$journalid. $nam . " a LEFT JOIN CTKTDT".$journalid. $nam . " b ON (a.idphieu = b.idphieu) WHERE (a.ngct BETWEEN '".$ngay1."' AND '".$ngay2."') and (a.tk like '".$tk."%'  or b.tkdu like '".$tk."%' )   Order by ngct, soct, id";
	  else if (!$ectkt && $ectktth)
	$query = 	
	   " SELECT @s:=@s+1 stt, '".$tk."' as tk, a.soct, a.ngct, b.diengiai, 'ktth' as macttkdu,  b.tkno, b.tkco,  b.sotienvn, b.idphieu as id
	   FROM (SELECT @s:= 0) AS s , ctktth".$journalid. $nam . "  a LEFT JOIN CTKTthdt".$journalid. $nam . " b ON (a.idphieu = b.idphieu) WHERE (a.ngct BETWEEN '".$ngay1."' AND '".$ngay2."') and (b.tkno like '".$tk."%'  or b.tkco like '".$tk."%' )
	   Order by ngct, soct, id";
	////
		$result = & $this->retrieve ( $query );

		$sectionsIterator = new DAOResultFactory ( $result, $this, '_returnCTKTTH_socai_FromRow' );

		$sections = $returner;
		$k = 0;
		if ($noco == "NO")
			while ( ($section = & $sectionsIterator->next ()) ) {
				$tkno = $section->gettkno ();
				if (strpos($tkno, $tk) ===0)
				{
					$i = $section->getidphieu ();	
					$tkco = $section->gettkco ();
					$sotien = $section->getsotienvn ();
					if (!in_array($tkco, $sections ['tk']))
						{
						$sections ['tk'][$k] =$tkco;
						$k += 1;
						}

					
					$sections [$i] ['ngct'] = $section->getngct ();
					$sections [$i] ['soct'] = $section->getsoct ();
					$sections [$i] ['diengiai'] = $section->getdiengiai ();
					$sections [$i] ['sotienvn'] = $sections [$i] ['sotienvn'] + $sotien;
					$sections [$i] [$tkco] = $sotien;
					$sections ['tc'][$tkco] = $sections ['tc'][$tkco] + $sotien;
					$sections ['tc']['tc'] = $sections ['tc']['tc'] + $sotien;
					unset ( $section );
				}
			}
		else
			while ( ($section = & $sectionsIterator->next ()) ) {
				$tkco = $section->gettkco ();
				if (strpos($tkco, $tk) ===0)
					{
					$i = $section->getidphieu ();	
					$tkno = $section->gettkno ();
					$sotien = $section->getsotienvn ();
					if (!in_array($tkno, $sections ['tk']))
						{
						$sections ['tk'][$k] =$tkno;
						$k += 1;
						}

					
					$sections [$i] ['ngct'] = $section->getngct ();
					$sections [$i] ['soct'] = $section->getsoct ();
					$sections [$i] ['diengiai'] = $section->getdiengiai ();
					$sections [$i] ['sotienvn'] = $sections [$i] ['sotienvn'] + $sotien;
					$sections [$i] [$tkno] = $sotien;
					$sections ['tc'][$tkno] = $sections ['tc'][$tkno] + $sotien;
					$sections ['tc']['tc'] = $sections ['tc']['tc'] + $sotien;
					
					unset ( $section );
					}
			}

		return $sections;
		
	}





function &getCTKTTH_socai($journalid, $nam, $ngay1, $ngay2, $tk, $ectkt, $ectktth) {
	if ($ectkt && $ectktth)
	{
	$query = 	
	   "SELECT @s:=@s+1 stt,  a.soct, a.ngct, b.diengiai,  b.macttkdu as macttk, 
		CASE a.noco
		   WHEN 'N' THEN a.tk
		   WHEN 'C' THEN b.tkdu
		   END AS tkno,
	  CASE a.noco
		   WHEN 'N' THEN b.tkdu
		   WHEN 'C' THEN a.tk
		   END AS tkco,
		   b.sotienvn, b.id
	   FROM (SELECT @s:= 0) AS s , CTKT".$journalid. $nam . " a LEFT JOIN CTKTDT".$journalid. $nam . " b ON (a.idphieu = b.idphieu) WHERE (a.ngct BETWEEN '".$ngay1."' AND '".$ngay2."') and (a.tk like '".$tk."%'  or b.tkdu like '".$tk."%' )
	union
	SELECT @s:=@s+1 stt, a.soct, a.ngct, b.diengiai, 'ktth' as macttkdu,  b.tkno, b.tkco,  b.sotienvn, b.idphieu as id
	   FROM (SELECT @s:= 0) AS s , ctktth".$journalid. $nam . "  a LEFT JOIN CTKTthdt".$journalid. $nam . " b ON (a.idphieu = b.idphieu) WHERE (a.ngct BETWEEN '".$ngay1."' AND '".$ngay2."') and (b.tkno like '".$tk."%'  or b.tkco like '".$tk."%' )
	   Order by ngct, soct, id";
	}
	elseif ($ectkt && !$ectktth)
		$query = 	
	   "SELECT @s:=@s+1 stt, '".$tk."' as tk,  a.soct, a.ngct, b.diengiai,  b.macttkdu as macttk, 
		CASE a.noco
		   WHEN 'N' THEN a.tk
		   WHEN 'C' THEN b.tkdu
		   END AS tkno,
	  CASE a.noco
		   WHEN 'N' THEN b.tkdu
		   WHEN 'C' THEN a.tk
		   END AS tkco,
		   b.sotienvn, b.id
	   FROM (SELECT @s:= 0) AS s , CTKT".$journalid. $nam . " a LEFT JOIN CTKTDT".$journalid. $nam . " b ON (a.idphieu = b.idphieu) WHERE (a.ngct BETWEEN '".$ngay1."' AND '".$ngay2."') and (a.tk like '".$tk."%'  or b.tkdu like '".$tk."%' )   Order by ngct, soct, id";
	  else if (!$ectkt && $ectktth)
	$query = 	
	   " SELECT @s:=@s+1 stt, '".$tk."' as tk, a.soct, a.ngct, b.diengiai, 'ktth' as macttkdu,  b.tkno, b.tkco,  b.sotienvn, b.idphieu as id
	   FROM (SELECT @s:= 0) AS s , ctktth".$journalid. $nam . "  a LEFT JOIN CTKTthdt".$journalid. $nam . " b ON (a.idphieu = b.idphieu) WHERE (a.ngct BETWEEN '".$ngay1."' AND '".$ngay2."') and (b.tkno like '".$tk."%'  or b.tkco like '".$tk."%' )
	   Order by ngct, soct, id";

		$result = & $this->retrieve ( $query );

		$returner = new DAOResultFactory ( $result, $this, '_returnCTKTTH_socai_FromRow' );
		return $returner;
	}


function &_returnCTKTTH_socai_FromRow(&$row) {
		$CTKTTH = new CTKTTH();
		$CTKTTH ->setidphieu($row['idphieu']);
		$CTKTTH->setsoct($row['soct']);
		$CTKTTH->setngct($row['ngct']);
		$CTKTTH->setdiengiai($row['diengiai']);
		$CTKTTH->setmacttk($row['macttk']);
		$CTKTTH->settkno($row['tkno']);
		$CTKTTH->settkco($row['tkco']);
		$CTKTTH->settkdu($row['tkno'], $row['tkco'], $gctk);
		$CTKTTH->settk($row['tk']);
		$CTKTTH->setnoco($row['tkno'], $row['tkco'], $row['tk']);
		
		$CTKTTH->setsotienvn($row['sotienvn']);

		return $CTKTTH;
	}

function &getCTKTTH_socai_PSDK_TMTG($journalid, $nam, $ngay1, $ngay2, $tk) {

		$query =   "SELECT sum(if(
		(noco = 'N' && tk like '".$tk."%') or (noco = 'C' && tkdu like '".$tk."%'),  b.sotienvn,0)) as psnovn, sum(if(
		(noco = 'C' and tk like '".$tk."%') or (noco = 'N' && tkdu like '".$tk."%'),
		b.sotienvn,0)) as pscovn FROM CTKT".$journalid. $nam."  a LEFT JOIN ctktdt".$journalid. $nam . " b  ON (a.idphieu = b.idphieu)  WHERE DATE( a.ngct ) < '". $ngay1.  "'";

		$result = & $this->retrieve ( $query );
		$result=$result->GetRowAssoc(false);
		return $result;

}

function &getCTKTTH_socai_PSDK_KTTH($journalid, $nam, $ngay1, $ngay2, $tk) {

		$query =   "SELECT sum(if(
		tkno like '".$tk."%',  b.sotienvn,0)) as psnovn, sum(if(
		tkco like '".$tk."%', b.sotienvn,0)) as pscovn FROM CTKTTH".$journalid. $nam."  a LEFT JOIN ctktthdt".$journalid. $nam . " b  ON (a.idphieu = b.idphieu)  WHERE DATE( a.ngct ) < '". $ngay1.  "' ";

		$result = & $this->retrieve ( $query );
		$result=$result->GetRowAssoc(false);
		return $result;

}


function &getCTKTTH_socai_SDDK($journalid, $nam, $ngay1, $ngay2, $tk) {
		$sections = $returner;
		$query = "SELECT sum(dknovn) as dknovn, sum(dkcovn) as dkcovn FROM SDTK" .$journalid. $nam ;
		$query = $query . " WHERE MATK like '" . $tk . "%' ";
		$result = & $this->retrieve ( $query );
		$result=$result->GetRowAssoc(false);
		return $result;
}




	function &_returnCTKTTH_BCTMTG_FromRow(&$row) {
		$CTKTTH = new CTKTTH();
		$CTKTTH ->setidphieu($row['idphieu']);
		$CTKTTH->setsoct($row['soct']);
		$CTKTTH->setngct($row['ngct']);
		$CTKTTH->setngayct($row['ngayct']);
		$CTKTTH->sethoten($row['hoten']);
		$CTKTTH->setdiachi($row['diachi']);
		$CTKTTH->setlydo($row['lydo']);
		$CTKTTH->setsolgctg($row['solgctg']);
		$CTKTTH->setmant($row['mant']);
		$CTKTTH->settigia($row['tigia']);
		$CTKTTH->settk($row['tk']);
		$CTKTTH->settkdu($row['tkdu']);
		$CTKTTH->setmacttkdu($row['macttkdu']);
		$CTKTTH->setsotienvn($row['sotienvn']);
		$CTKTTH->setsotiennt($row['sotiennt']);
		$CTKTTH->setdiengiai($row['diengiai']);
		$CTKTTH->setnoco($row['noco']);
		//HookRegistry::call('CTKTTHDAO::_returnCTKTTH_BCTMTG_FromRow', array(&$CTKTTH, &$row));
		return $CTKTTH;

	}


	function &getCTKTTH_ThuChi($query) {
		$result = & $this->retrieve ( $query );
		$sections = $returner ;
		$i = 0;
		while (!$result->EOF) {
			$rows = $result->GetRowAssoc(false);
			$pmatk = $rows['matk'];
			$ptentk = $rows['tentk'];
			$sections[$pmatk."-".$ptentk] = $pmatk."-".$ptentk;

			if ($i==0)
			{
				$sections [0] ['ngct'] = $rows['ngct'] ;
				$sections [0] ['soct'] = $rows['soct'] ;
				$sections [0] ['hoten'] = $rows['hoten'] ;
				$sections [0] ['diachi'] = $rows['diachi'] ;
				$sections [0] ['lydo'] = $rows['lydo'] ;
				$sections [0] ['solgctg'] = $rows['solgctg'] ;
				$sections [0] ['tkdu'] = $rows['tkdu'] ;
				$sections [0] ['tk'] = $rows['tk'] ;
				$sections [0] ['sotienvn'] = floatval($rows['sotienvn']) ;
			}
			else
			{
				$sections [$i] ['tkdu'] = $rows['tkdu'];
				$cotkdu = 0;
				for ($x=0; $x <$i; $x++) {
					if ($sections [$i] ['tkdu'] != $sections [$x] ['tkdu'])
					{
						$cotkdu = 1;
					}
										
				} 
				if ($cotkdu == 1)
					$sections [0] ['tkdu'] .= "-" .  $rows['tkdu'];

				$sections [0] ['sotienvn'] += floatval($rows['sotienvn']) ;


			}
			$i += 1;

			unset($section);
			$result->moveNext();
		}
 	   return $sections;
	}

	function &_returnCTKTTH_ThuChi_FromRow(&$row) {
		$CTKTTH = new CTKTTH();
		$CTKTTH->setsoct($row['soct']);
		$CTKTTH->setngct($row['ngct']);
		$CTKTTH->sethoten($row['hoten']);
		$CTKTTH->setdiachi($row['diachi']);
		$CTKTTH->setlydo($row['lydo']);
		$CTKTTH->setsolgctg($row['solgctg']);
		$CTKTTH->settk($row['tk']);
		$CTKTTH->settkdu($row['tkdu']);
		$CTKTTH->setsotienvn($row['sotienvn']);
		return $CTKTTH;

	}

	function &_returnSDTT_FromRow(&$row) {
		$CTKTTH = new CTKTTH();
		$CTKTTH->setdknovn($row['dknovn']);
		$CTKTTH->setdknont($row['dknont']);
				return $CTKTTH;

	}

	
	function CreateTable($Year, $journalid ) {
		$sql = "CREATE TABLE CTKTTH".$journalid.$Year." LIKE CTKTTHstr" ;
		$result =& $this->retrieve($sql );
		$sql = "CREATE TABLE CTKTTHDT".$journalid.$Year." LIKE CTKTTHDTstr" ;
		$result =& $this->retrieve($sql );


		return;

	}



}

?>
